﻿using Microsoft.Data.Sqlite;

namespace WhaleAccount.Helpers;
public class DataAccess
{
    private readonly string dbpath;

    public DataAccess(string dbpath)
    {
        this.dbpath = dbpath;

        if (!File.Exists(dbpath))
        {
            using var file = File.Create(dbpath);
        }
    }

    public void InitializeDatabase()
    {
        using var db = new SqliteConnection($"Filename={dbpath}");
        db.Open();

        var tableCommand = "CREATE TABLE IF NOT " +
            "EXISTS MyTable (Primary_Key INTEGER PRIMARY KEY, " +
            "Text_Entry NVARCHAR(2048) NULL)";

        var createTable = new SqliteCommand(tableCommand, db);

        createTable.ExecuteReader();
    }

    public void AddData(string inputText)
    {
        using var db = new SqliteConnection($"Filename={dbpath}");
        db.Open();

        var insertCommand = new SqliteCommand
        {
            Connection = db,

            // Use parameterized query to prevent SQL injection attacks
            CommandText = "INSERT INTO MyTable VALUES (NULL, @Entry);"
        };
        insertCommand.Parameters.AddWithValue("@Entry", inputText);

        insertCommand.ExecuteReader();
    }

    public List<string> GetData()
    {
        var entries = new List<string>();

        using (var db = new SqliteConnection($"Filename={dbpath}"))
        {
            db.Open();

            var selectCommand = new SqliteCommand
                ("SELECT Text_Entry from MyTable", db);

            var query = selectCommand.ExecuteReader();

            while (query.Read())
            {
                entries.Add(query.GetString(0));
            }
        }

        return entries;
    }
}
